# coding=gbk

from datetime import date, timedelta
from DataAccess.DBConnFactory import DBConnFactory
from Import.TradeImport import *
from Import.PositionImport import *
from Position.CalcDailyPosition import *
from Recover.PositionRecover import *
from Config.ImportConfig import *

def test_case3():
	conn = DBConnFactory().get_db_connection('PKEDB')
	cursor = conn.cursor()
	
	#clear db data
	clear_all()
	print 'info, clear all data complete.'

	#add portfolio info entry
	sql_text = '''delete from portfolio_info 
				where portfolio_id='SCTest' or portfolio_id='SCTest1' or portfolio_id='SCTest2' '''
	cursor.execute(sql_text)
	conn.commit()
	sql_text = '''insert into portfolio_info(portfolio_id,"name",inception_date,inception_val,currency,manager,"type",cash_account) values 
				('SCTest','SCTest',TO_DATE('2011-12-26','yyyy-mm-dd'),0,'USD','XieBing','PUBLIC',0)'''
	cursor.execute(sql_text)
	sql_text = '''insert into portfolio_info(portfolio_id,"name",inception_date,inception_val,currency,manager,"type",cash_account,parent_portf) 
				values(:1,:2,TO_DATE(:3,'yyyy-mm-dd'),:4,:5,:6,:7,:8,:9)  '''
	v =			[('SCTest1','SCTest1','2011-12-26',0,'USD','XieBing1','INTERNAL',0,'SCTest'),
				('SCTest2','SCTest2','2011-12-26',0,'USD','XieBing2','INTERNAL',0,'SCTest')]
	cursor.executemany(sql_text, v)
	conn.commit()
	print 'info, add test portfolio info complete.'
	
	sql_text = '''insert into position
				(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7) '''
	v =			[('2011-12-26','SCTest', '9999999','CASH',0,1.0,'USD'), 
				('2011-12-26','SCTest', '9999999','CASH',0,1.0,'CNY'), 
				('2011-12-26','SCTest', '9999999','CASH',0,1.0,'HKD'), 
				('2011-12-26','SCTest1','9999999','CASH',0,1.0,'USD'),  
				('2011-12-26','SCTest1','9999999','CASH',0,1.0,'CNY'), 
				('2011-12-26','SCTest1','9999999','CASH',0,1.0,'HKD'), 
				('2011-12-26','SCTest2','9999999','CASH',0,1.0,'USD'), 
				('2011-12-26','SCTest2','9999999','CASH',0,1.0,'CNY'), 
				('2011-12-26','SCTest2','9999999','CASH',0,1.0,'HKD')]
	cursor.executemany(sql_text, v)
	conn.commit()
	print 'info, add initial position complete.'	
		
	trade_date = date(2011, 12, 27)	
	sql_text = '''insert into transaction(trade_date,ticker,trade_type,portfolio_id,security_type,amount,cost_price,currency) 
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7,:8) '''
	v =			[('2011-12-27','600000','BUY','SCTest1','STOCK',500,7.00,'CNY'), 
				('2011-12-27','08102','SHORT SELLING','SCTest2','STOCK',100,3.00,'HKD')]
	cursor.executemany(sql_text, v)
	conn.commit()
	
	import_daily_trades(trade_date)
	update_position_driven_by_dividend(trade_date)	
	update_position_driven_by_transaction(trade_date)
	update_position_driven_by_repo_expire(trade_date)
	print 'info, calculate 2012-12-27 position complete.'
	
	sql_text = '''delete from portfolio_info 
				where portfolio_id='SCTest' or portfolio_id='SCTest1' or portfolio_id='SCTest2' '''
	cursor.execute(sql_text)
	conn.commit()

	
	